<?php
/*
 * author    : 秦刚
 * created   : 2009-6-12
 * 
 * 数据表文件，简单的封装了几个能用表操作
 */
require_once FRAME_ROOT_PATH . '/Exception/DbException.php';
class Table
{
    protected $tablePrefix = '';
    protected $tableName = '';
    protected $db = '';
    protected $cache = '';
    /**
     * $hashOne,是一个用映射查询的设置，当前表的每一条记录都拥有最多一条（0–1）关联记录 
     * $hasOne = array(array(
     * 'foreignTableName' => 'Profiles',
     * 'associateKey'=> 'user_id',
     * 'foreignKey' => 'user_id',
     * 'mappingName' => 'profile'));
     * 
     */
    protected $hashOne = array();
    /**
     * $hashMany,
     * 是一个用映射查询的设置，当前表的每一条记录都拥有多条（0-n）关联记录
     * $hasMany = array(
     * 'foreignTableName' => 'Profiles',
     * 'associateKey'=> 'user_id',
     * 'foreignKey' => 'user_id',
     * 'mappingName' => 'profile');
     */
    protected $hashMany = array();

    public function __construct ($db, $cache = null)
    {
        $this->db = $db;
        $this->cache = $cache;
    }

    public function setDb ($db)
    {
        $this->db = $db;
    }

    public function getDb ()
    {
        return $this->db;
    }

    public function setCache ($cache)
    {
        $this->cache = $cache;
    }

    public function getCache ()
    {
        return $this->cache;
    }

    public function getTablePrefix ()
    {
        return $this->tablePrefix;
    }

    public function setTableName ($tableName)
    {
        $this->tableName = $tableName;
    }

    public function getTableName ()
    {
        return $this->tableName;
    }

    /**
     * 插入单条数据
     *
     * @param array $values 键值对
     * @param $returnInsertId 是否返回自增id
     * 
     * @return void | last_insert_id
     * @throws DbException
     */
    public function insert (array $values, $returnInsertId = false)
    {
        return $this->db->insert($this->tablePrefix . $this->tableName, $values, $returnInsertId);
    }

    /**
     * 修改数据
     *
     * @param array $newValues 新的值，键值对  array ('name' => 'name', 'num' => 'num+1')
     * 
     * @param $condition 条件，可以是字符串，
     * 也可以是数据如'字段="值"'或array('字段'=>'值',1=>'字段="值"')
     * 
     * @param $affectRows 是否返回修改的行数
     *
     * @return void | 修改的行数
     * @throws DbException
     */
    public function update (array $newValues, $condition, $affectRows = false)
    {
        $where = $this->getCondition($condition);
        return $this->db->update($this->tablePrefix . $this->tableName, $newValues, $where, $affectRows);
    }

    /**
     * 返回一行数据
     * 
     * @param $feild ,查询字段
     * @param $condition 条件，可以是字符串，
     * 也可以是数组，如'字段="值"'或array('字段'=>'值',1=>'字段="值"')
     *
     * @return Array var 若无数据，则返回false
     * @throws DbException
     */
    public function fetchRow ($field = ' * ', $condition = '', $order = '')
    {
        $result = array();
        $fieldString = ' * ';
        $listorder = '';
        if (is_string($order)) {
            if (! empty($order)) {
                $listorder = ' order by ' . $order;
            }
        }
        if (empty($field) || ! is_string($field)) {
            $fieldString = ' * ';
        }
        else {
            $fieldString = $field;
        }
        $where = $this->getCondition($condition);
        $sql = 'select ' . $fieldString . ' from ' . $this->tablePrefix . $this->tableName . $where . $listorder;
        $result = $this->db->fetchRow($sql);
        $foreignData = array();
        if (! empty($this->hashOne)) {
            foreach ($this->hashOne as $value) {
                $sql = 'select * from ' . $this->tablePrefix . $value['foreignTableName'] . ' where ' . $value['foreignKey'] . '="' . $result[$value['associateKey']] . '"';
                $foreignData = $this->db->fetchRow($sql);
                if (! empty($foreignData)) {
                    $result[$value['mappingName']] = $foreignData;
                }
                else {
                    $result[$value['mappingName']] = array();
                }
            }
        }
        return $result;
    }

    /**
     * 返回所有数据,以某一列作为键名,要慎用些函数，作为key的字段的值
     * 必须是唯一的，否则会有数据被覆盖的可能
     *
     * @param string $field 查询字段
     * @param string|array $condition 条件，
     * 如'字段="值"'或array('字段'=>'值',1=>'字段="值"')
     * 
     * @param string $order 排序
     * @param string $col 列名
     * @return array
     */
    public function fetchAssoc ($field = ' * ', $condition = '', $order = '', $col = '')
    {
        $result = array();
        $fieldString = ' * ';
        $listorder = '';
        if (is_string($order)) {
            if (! empty($order)) {
                $listorder = ' order by ' . $order;
            }
        }
        if (empty($field) || ! is_string($field)) {
            $fieldString = ' * ';
        }
        else {
            $fieldString = $field;
        }
        $where = $this->getCondition($condition);
        $sql = 'select ' . $fieldString . ' from ' . $this->tablePrefix . $this->tableName . $where . $listorder;
        //return $this->db->fetchAssoc($sql, '', $col);
        

        $query = $this->db->query($sql);
        while ($data = $this->db->fetchArray($query)) {
            if (empty($col)) {
                $col = key($data);
            }
            $result[$data[$col]] = $data;
            //hasOne处理
            $foreignData = array();
            if (! empty($this->hashOne)) {
                foreach ($this->hashOne as $value) {
                    $sql = 'select * from ' . $this->tablePrefix . $value['foreignTableName'] . ' where ' . $value['foreignKey'] . '="' . $data[$value['associateKey']] . '"';
                    $foreignData = $this->db->fetchRow($sql);
                    if (! empty($foreignData)) {
                        $result[$data[$col]][$value['mappingName']] = $foreignData;
                    }
                    else {
                        $result[$data[$col]][$value['mappingName']] = array();
                    }
                }
            
            }
        }
        return $result;
    }

    /**
     * 返回所有数据
     *
     * @param $field ,查询字段
     * @param $condition 条件，可以是字符串，
     * 也可以是数据如'字段="值"'或array('字段'=>'值',1=>'字段="值"')
     * @param $order 排序，如listorder asc
     *
     * @return Array var 若无数据，则返回array () 空数组
     * @throws DbException
     */
    public function fetchAll ($field = ' * ', $condition = '', $order = '')
    {
        $result = array();
        $fieldString = ' * ';
        $listorder = '';
        if (is_string($order)) {
            if (! empty($order)) {
                $listorder = ' order by ' . $order;
            }
        }
        if (empty($field) || ! is_string($field)) {
            $fieldString = ' * ';
        }
        else {
            $fieldString = $field;
        }
        $where = $this->getCondition($condition);
        $sql = 'select ' . $fieldString . ' from ' . $this->tablePrefix . $this->tableName . $where . $listorder;
        //return $this->db->fetchAll($sql);
        $query = $this->db->query($sql);
        $i = 0;
        while ($data = $this->db->fetchArray($query)) {
            $result[$i] = $data;
            $foreignData = array();
            if (! empty($this->hashOne)) {
                foreach ($this->hashOne as $value) {
                    $sql = 'select * from ' . $this->tablePrefix . $value['foreignTableName'] . ' where ' . $value['foreignKey'] . '="' . $data[$value['associateKey']] . '"';
                    $foreignData = $this->db->fetchRow($sql);
                    if (! empty($foreignData)) {
                        $result[$i][$value['mappingName']] = $foreignData;
                    }
                    else {
                        $result[$i][$value['mappingName']] = array();
                    }
                }
            
            }
            $i ++;
        }
        return $result;
    }

    /**
     * 删除
     *
     * @param $where 条件
     * @param $affectRows 是否返回修改的行数
     *
     * @return void | 删除的行数
     * @throws DbException
     */
    public function delete ($condition, $affectRows = false)
    {
        $where = $this->getCondition($condition);
        return $this->db->delete($this->tablePrefix . $this->tableName, $where, $affectRows);
    }

    /**
     * 查询条件处理函数，
     *
     * @param string|array $condition，当条件是字符串时，
     * 没有'where'关键字，就是'字段="值"'的样式，当为数组时，
     * 数组是一维数据，当索引是字符串时，则由key=value来组成
     * 条件，若索引是数字时，则由value组成条件
     * 
     * @return string 带有'where' 关键字
     */
    public function getCondition ($condition, $operation = 'and')
    {
        $where = '';
        if (empty($condition)) {
            return '';
        }
        if (is_string($condition)) {
            $where = ' where ' . $condition;
        }
        else if (is_array($condition)) {
            $i = 0;
            foreach ($condition as $key => $value) {
                if ($i == 0) {
                    if (is_numeric($key)) {
                        $where = ' where ' . $value;
                    }
                    else if (is_string($key)) {
                        $where = ' where ' . $key . '="' . $this->db->quote($value) . '" ';
                    }
                    else {
                        throw new DbException('array key is not  string or number');
                    }
                }
                else {
                    if (is_numeric($key)) {
                        $where .= ' ' . $operation . ' ' . $value;
                    }
                    else if (is_string($key)) {
                        $where .= ' ' . $operation . ' ' . $key . '="' . $this->db->quote($value) . '" ';
                    }
                    else {
                        throw new DbException('array key is not  string or number');
                    }
                }
                $i ++;
            }
        }
        else {
            $where = '';
        }
        return $where;
    }

    /**
     * 分页取数据
     *
     * @param string $field 要查询的字段，可以是字符串，也可以是数组
     * @param string|array $condition 查询条件，可以字符串，事先写好，也可以是数组，key为字段，value为值
     * @param string $order 排序字符串，如' id desc' ,表示按id降序排
     * @param number $startRecord 分页查询时，开始记录
     * @param number $pageSize 分页查询时，一页要取的条数
     * @return array
     */
    public function fetchPageData ($field, $condition = '', $order = '', $startRecord = 0, $pageSize = -1)
    {
        $result = array();
        $fieldString = '';
        $listorder = '';
        $limit = '';
        $where = $this->getCondition($condition);
        if (empty($field) || ! is_string($field)) {
            $fieldString = ' * ';
        }
        else {
            $fieldString = $field;
        }
        if (is_string($order) && ! empty($order)) {
            $listorder = ' order by ' . $order;
        }
        if (is_numeric($startRecord) && is_numeric($pageSize)) {
            if ($startRecord < 0) {
                $startRecord = 0;
            }
            if ($pageSize > 0) {
                $limit = ' limit ' . $startRecord . ' , ' . $pageSize;
            }
        }
        $sql = 'select ' . $fieldString . ' from ' . $this->tablePrefix . $this->tableName . $where . $listorder . $limit;
        //return $this->db->fetchAll($sql);
        

        $foreignData = array();
        $query = $this->db->query($sql);
        $i = 0;
        while ($data = $this->db->fetchArray($query)) {
            $result[$i] = $data;
            $foreignData = array();
            if (! empty($this->hashOne)) {
                foreach ($this->hashOne as $value) {
                    $sql = 'select * from ' . $this->tablePrefix . $value['foreignTableName'] . ' where ' . $value['foreignKey'] . '="' . $data[$value['associateKey']] . '"';
                    $foreignData = $this->db->fetchRow($sql);
                    if (! empty($foreignData)) {
                        $result[$i][$value['mappingName']] = $foreignData;
                    }
                    else {
                        $result[$i][$value['mappingName']] = array();
                    }
                }
            }
            $i ++;
        }
        return $result;
    }

    /**
     * 取提数据库中记录总数
     *
     * @param string|array $condition
     * @return int
     */
    public function fetchTotalNums ($condition = '')
    {
        $result = array();
        $where = $this->getCondition($condition);
        $sql = 'select count(*) as count from ' . $this->tablePrefix . $this->tableName . $where;
        $result = $this->db->fetchRow($sql);
        if (! empty($result)) {
            return $result['count'];
        }
        else {
            return 0;
        }
    }
}
?>